﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using System.Threading.Tasks;
using Community.CsharpSqlite.SQLiteClient;
using Windows.Storage;

namespace Local.Service
{
    public class LocalArticleService
    {
        public static LocalArticleService Service;
        public static SqliteConnection Connection;

        public static LocalArticleService Instance()
        {
            if (Service == null)
            {
                Service = new LocalArticleService();
                if (Connection == null)
                {
                    Connection = new SqliteConnection();

                    string dbFilename = Path.Combine(ApplicationData.Current.LocalFolder.Path, @"NReader1.db");
                    string cs = string.Format("Version=3,uri=file:{0}", dbFilename);
                    if (!Utils.FileExists(dbFilename))
                    {
                        Connection.ConnectionString = cs;
                        Connection.Open();
                        CreateTables();
                    }
                    else
                    {
                        Connection.ConnectionString = cs;
                        Connection.Open();
                    }
                }
            }
            return Service;
        }

        private static void CreateTables()
        {
            //create table
            IDbCommand cmd = Connection.CreateCommand();
            var sql = new StringBuilder();

            sql.Append(" CREATE TABLE [metro_article] (");
            sql.Append(" [articleid] INT(11) NOT NULL, ");
            sql.Append(" [lastupdate] INT(11), ");
            sql.Append(" [articlename] VARCHAR(30), ");
            sql.Append(" [author] VARCHAR(30), ");
            sql.Append(" [keywords] VARCHAR(50), ");
            sql.Append(" [sortid] INT, ");
            sql.Append(" [intro] VARCHAR(300), ");
            sql.Append(" [lastchapterid] INT(11), ");
            sql.Append(" [lastchapter] VARCHAR(100), ");
            sql.Append(" [chapters] INT, ");
            sql.Append(" [display] INT, ");
            sql.Append(" [sortname] VARCHAR(30), ");
            sql.Append(" [groupname] VARCHAR(30), ");
            sql.Append(" [writeprogress] VARCHAR(30), ");
            sql.Append(" [updatetime] INT, ");
            sql.Append(" [coverlink] VARCHAR(100), ");
            sql.Append(" CONSTRAINT [sqlite_autoindex_metro_article_1] PRIMARY KEY ([articleid]));");

            sql.Append("   CREATE TABLE [metro_bookstore] (");
            sql.Append("   [articleid] INT(11) NOT NULL, [updatetime] INT, ");
            sql.Append("   CONSTRAINT [sqlite_autoindex_metro_bookstore_1] PRIMARY KEY ([articleid]));");

            //sql.Append(" CREATE TABLE [metro_sysweb] (");
            //sql.Append(" [webflag] VARCHAR(10) , ");
            //sql.Append(" [webname] VARCHAR(50) , ");
            //sql.Append(" [sortid] int(11) , ");
            //sql.Append(" [matchimage] VARCHAR(300) , ");
            //sql.Append(" [matchtext] VARCHAR(300) , ");
            //sql.Append(" [filtertext] VARCHAR(300),encoding VARCHAR(10) );");
            //sql.Append(" CREATE INDEX [index_metro_sysweb_1] ON [metro_sysweb] ([webflag]);");

            //sql.Append(" CREATE TABLE [metro_index] (");
            //sql.Append(" [articleid] INT(11) NOT NULL, ");
            //sql.Append(" [articlename] VARCHAR(30), ");
            //sql.Append(" [lastchapter] VARCHAR(100), ");
            //sql.Append(" [groupname] VARCHAR(30), ");
            //sql.Append(" [groupid] INT );");
            //sql.Append(" CONSTRAINT [sqlite_autoindex_metro_article_2] PRIMARY KEY ([articleid]));");
            cmd.CommandText = sql.ToString();
            cmd.ExecuteNonQuery();
        }

        public async Task<bool> AddBookStore(int articleid)
        {
            try
            {
                IDbCommand cmd = Connection.CreateCommand();
                cmd.CommandText =
                    string.Format(
                        " delete from metro_bookstore where articleid={0}; insert into metro_bookstore(articleid,updatetime) values({0},{1});",
                        articleid.ToString(), DateTime.Now.ToString("yyyyMMddHHmmss"));
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
                throw;
            }
        }

        public async  Task<IList<ArticleInfo>> GetBookStore()
        {
            IList<ArticleInfo> list = new List<ArticleInfo>();
            IDbCommand cmd = Connection.CreateCommand();
            cmd.CommandText = "select a.articleid,lastupdate,articlename,keywords,author,sortid,intro," +
                              "lastchapterid,lastchapter,chapters,display,sortname,coverlink,writeprogress " +
                              "from metro_bookstore as a left join metro_article as b on a.articleid=b.articleid ";
            var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                var model = new ArticleInfo
                                {
                                    articleid = reader.GetInt32(0),
                                    lastupdate = reader.GetInt32(1),
                                    articlename = reader.GetString(2),
                                    keywords = reader.GetString(3),
                                    author = reader.GetString(4),
                                    sortid = reader.GetInt32(5),
                                    intro = reader.GetString(6),
                                    lastchapterid = reader.GetInt32(7),
                                    lastchapter = reader.GetString(8),
                                    chapters = reader.GetInt32(9),
                                    display = reader.GetInt32(10),
                                    sortname = reader.GetString(11),
                                    coverlink = reader.GetString(12),
                                    writeprogress = reader.GetString(13)
                                };
                list.Add(model);
            }
            return list;
        }

        public int GetBookStoreCount()
        {
            IDbCommand cmd = Connection.CreateCommand();
            cmd.CommandText = " select count(articleid)" +
                              " from metro_bookstore ";
            var reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                return reader.GetInt32(0);
            }
            return 0;
        }

        public bool DeleteBookStore(string articleids)
        {
            IDbCommand cmd = Connection.CreateCommand();
            var strSql = new StringBuilder();
            strSql.Append(" delete from metro_bookstore where articleid in " + articleids + ";");
            cmd.CommandText = strSql.ToString();
            return cmd.ExecuteNonQuery() >= 1;
        }

        public ArticleInfo GetArticle(int articleid)
        {
            IDbCommand cmd = Connection.CreateCommand();
            cmd.CommandText = "select a.articleid,lastupdate,articlename,keywords,author,sortid,intro," +
                              "lastchapterid,lastchapter,chapters,display,sortname,coverlink,writeprogress " +
                              "from metro_bookstore as a left join metro_article as b on a.articleid=b.articleid " +
                              "where a.articleid=@articleid";
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@articleid", Value = articleid});
            var reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                var model = new ArticleInfo
                                {
                                    articleid = articleid,
                                    lastupdate = reader.GetInt32(1),
                                    articlename = reader.GetString(2),
                                    keywords = reader.GetString(3),
                                    author = reader.GetString(4),
                                    sortid = reader.GetInt32(5),
                                    intro = reader.GetString(6),
                                    lastchapterid = reader.GetInt32(7),
                                    lastchapter = reader.GetString(8),
                                    chapters = reader.GetInt32(9),
                                    display = reader.GetInt32(10),
                                    sortname = reader.GetString(11),
                                    coverlink = reader.GetString(12),
                                    writeprogress = reader.GetString(13)
                                };
                return model;
            }
            return null;
        }

        public async Task<bool> AddBook(ArticleInfo article)
        {
            IDbCommand cmd = Connection.CreateCommand();
            var strSql = new StringBuilder();
            strSql.Append(" delete from metro_article where articleid=@articleid;");
            strSql.Append(" insert into metro_article(");
            strSql.Append(" articleid,lastupdate,articlename,keywords,author,");
            strSql.Append(" sortid,intro,lastchapterid,lastchapter,chapters,");
            strSql.Append(" display,coverlink,writeprogress,sortname,updatetime)");
            strSql.Append(" values(@articleid,@lastupdate,@articlename,@keywords,@author,");
            strSql.Append(" @sortid,@intro,@lastchapterid,@lastchapter,@chapters,");
            strSql.Append(" @display,@coverlink,@writeprogress,@sortname,@updatetime)");
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@articleid", Value = article.articleid});
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@lastupdate", Value = article.lastupdate});
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@articlename", Value = article.articlename});
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@keywords", Value = article.keywords});
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@author", Value = article.author});
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@sortid", Value = article.sortid});
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@intro", Value = article.intro});
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@lastchapterid", Value = article.lastchapterid});
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@lastchapter", Value = article.lastchapter});
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@chapters", Value = article.chapters});
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@display", Value = article.display});
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@coverlink", Value = article.coverlink});
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@writeprogress", Value = article.writeprogress});
            cmd.Parameters.Add(new SqliteParameter {ParameterName = "@sortname", Value = article.sortname});
            cmd.Parameters.Add(new SqliteParameter
                                   {ParameterName = "@updatetime", Value = DateTime.Now.ToString("yyyyMMddHHmmss")});
            cmd.CommandText = strSql.ToString();
            return cmd.ExecuteNonQuery() == 1;
        }

        public async Task<IList<ArticleInfo>> GetHistoryList(int count)
        {
            IList<ArticleInfo> list = new List<ArticleInfo>();
            IDbCommand cmd = Connection.CreateCommand();
            cmd.CommandText = "select articleid,lastupdate,articlename,keywords,author,sortid,intro," +
                              "lastchapterid,lastchapter,chapters,display,sortname,coverlink,writeprogress " +
                              "from metro_article order by updatetime desc limit 0,@count";
            cmd.Parameters.Add(new SqliteParameter { ParameterName = "@count", Value = count });
            var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                var model = new ArticleInfo
                {
                    articleid = reader.GetInt32(0),
                    lastupdate = reader.GetInt32(1),
                    articlename = reader.GetString(2),
                    keywords = reader.GetString(3),
                    author = reader.GetString(4),
                    sortid = reader.GetInt32(5),
                    intro = reader.GetString(6),
                    lastchapterid = reader.GetInt32(7),
                    lastchapter = reader.GetString(8),
                    chapters = reader.GetInt32(9),
                    display = reader.GetInt32(10),
                    sortname = reader.GetString(11),
                    coverlink = reader.GetString(12),
                    writeprogress = reader.GetString(13),
                    GroupName = "浏览记录"
                };
                list.Add(model);
            }
            return list;
        }


    }
}

